package com.oner365.test.sql;

import java.io.File;
import java.util.ArrayList;
import java.util.List;
import java.util.concurrent.atomic.AtomicInteger;

import org.apache.commons.io.FileUtils;
import org.apache.commons.lang3.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.oner365.test.cast.CastTest;

public class SqlFormat {

  private static final Logger LOGGER = LoggerFactory.getLogger(CastTest.class);

  public static void main(String[] args) {
    String filePath = "src/main/java/com/oner365/test/sql/init.sql";
    getSqlFormatList(filePath);
  }

  public static List<String> getSqlFormatList(String filePath) {
    File file = new File(filePath);
    try {
      List<String> allList = new ArrayList<String>();
      List<String> list = FileUtils.readLines(file, "UTF-8");
      AtomicInteger index = new AtomicInteger(0);
      AtomicInteger sequenceStart = new AtomicInteger(0);
      AtomicInteger sequenceEnd = new AtomicInteger(0);
      AtomicInteger tableStart = new AtomicInteger(0);
      AtomicInteger tableEnd = new AtomicInteger(0);
      AtomicInteger commentStart = new AtomicInteger(0);
      AtomicInteger commentEnd = new AtomicInteger(0);
      AtomicInteger functionStart = new AtomicInteger(0);
      AtomicInteger functionEnd = new AtomicInteger(0);
      AtomicInteger insertStart = new AtomicInteger(0);
      AtomicInteger insertEnd = new AtomicInteger(0);
      AtomicInteger alterStart = new AtomicInteger(0);
      AtomicInteger alterEnd = new AtomicInteger(0);
      list.stream().forEach(s -> {
        index.getAndIncrement();
        if (s.contains("-- sequence start")) {
          sequenceStart.addAndGet(index.get());
        }
        if (s.contains("-- sequence end")) {
          sequenceEnd.addAndGet(index.get());
        }
        if (s.contains("-- table start")) {
          tableStart.addAndGet(index.get());
        }
        if (s.contains("-- table end")) {
          tableEnd.addAndGet(index.get());
        }
        if (s.contains("-- comment start")) {
          commentStart.addAndGet(index.get());
        }
        if (s.contains("-- comment end")) {
          commentEnd.addAndGet(index.get());
        }
        if (s.contains("-- function start")) {
          functionStart.addAndGet(index.get());
        }
        if (s.contains("-- function end")) {
          functionEnd.addAndGet(index.get());
        }
        if (s.contains("-- insert start")) {
          insertStart.addAndGet(index.get());
        }
        if (s.contains("-- insert end")) {
          insertEnd.addAndGet(index.get());
        }
        if (s.contains("-- alter start")) {
          alterStart.addAndGet(index.get());
        }
        if (s.contains("-- alter end")) {
          alterEnd.addAndGet(index.get());
        }
      });
//      allList.addAll(getSequenceSql(list.subList(sequenceStart.get(), sequenceEnd.get())));
//      allList.addAll(getTableSql(list.subList(tableStart.get(), tableEnd.get())));
//      allList.addAll(getCommentSql(list.subList(commentStart.get(), commentEnd.get())));
//      allList.addAll(getFunctionSql(list.subList(functionStart.get(), functionEnd.get())));
      allList.addAll(getInsertSql(list.subList(insertStart.get(), insertEnd.get())));
//      allList.addAll(getAlterSql(list.subList(alterStart.get(), alterEnd.get())));
//      allList.stream().forEach(s -> System.out.println(s));
      return allList;
    } catch (Exception e) {
      LOGGER.error("getSqlFormatList error:", e);
    }
    return null;

  }

  public static List<String> getSequenceSql(List<String> sequenceList) {
    List<String> list = new ArrayList<String>();
    StringBuilder str = new StringBuilder();
    sequenceList.stream().forEach(s -> {
      if (StringUtils.isNotEmpty(s) && !s.trim().startsWith("--")) {
        str.append(s.replaceAll("`", "") + " ");
      }
      if (s.endsWith(";")) {
        list.add(str.toString());
        str.delete(0, str.length());
      }
    });
    return list;
  }

  public static List<String> getTableSql(List<String> tableList) {
    List<String> list = new ArrayList<String>();
    StringBuilder str = new StringBuilder();
    tableList.stream().forEach(s -> {
      if (StringUtils.isNotEmpty(s) && !s.trim().startsWith("--")) {
        str.append(s.replaceAll(s, "`") + " ");
      }
      if (s.endsWith(";")) {
        list.add(str.toString());
        str.delete(0, str.length());
      }
    });
    return list;
  }

  public static List<String> getFunctionSql(List<String> functionList) {
    List<String> list = new ArrayList<String>();
    StringBuilder str = new StringBuilder();
    functionList.stream().forEach(s -> {
      if (StringUtils.isNotEmpty(s) && !s.trim().startsWith("--") && !s.equals("/")) {
        str.append(s.replaceAll("`", "") + " ");
      }
      if (s.endsWith("/")) {
        list.add(str.toString());
        str.delete(0, str.length());
      }
    });
    return list;
  }

  public static List<String> getInsertSql(List<String> insertList) {
    List<String> list = new ArrayList<String>();
    StringBuilder str = new StringBuilder();
    insertList.stream().forEach(s -> {
      if (StringUtils.isNotEmpty(s) && !s.trim().startsWith("--") && !s.equals("/")) {
        str.append(s.replaceAll("`", "") + " ");
      }
      if (s.endsWith(");")) {
        String col = StringUtils.substringBetween(str.toString().toUpperCase(),"INTO " , "VALUES");
        System.out.println(col);
//        str. 
        list.add(str.toString());
        str.delete(0, str.length());
      }
    });
    return list;
  }

  public static List<String> getAlterSql(List<String> alterList) {
    List<String> list = new ArrayList<String>();
    StringBuilder str = new StringBuilder();
    alterList.stream().forEach(s -> {
      if (StringUtils.isNotEmpty(s) && !s.trim().startsWith("--")) {
        str.append(s.replaceAll("`", "") + " ");
      }
      if (s.endsWith(";")) {
        list.add(str.toString());
        str.delete(0, str.length());
      }
    });
    return list;
  }

  public static List<String> getCommentSql(List<String> commentList) {
    List<String> list = new ArrayList<String>();
    StringBuilder str = new StringBuilder();
    commentList.stream().forEach(s -> {
      if (StringUtils.isNotEmpty(s) && !s.trim().startsWith("--")) {
        str.append(s.replaceAll("`", "") + " ");
      }
      if (s.endsWith(";")) {
        list.add(str.toString());
        str.delete(0, str.length());
      }
    });
    return list;
  }
}
